

/*
clean low frequency data: datastream, FRED, and GSW yield
input data comes from proprietary sources

output:
- ../data/fred/_fred*: raw fred data
- datastream_daily_shocks.dta: combined datastream data, daily changes
- fred_daily_shocks.dta: combined fred data, daily changes
- fred_balance_sheet.dta: balance sheet data of the fed
- fred_treasury_rates.dta: fred constant maturity treasury rates
- nss_levels.dta, nss_shocks.dta: levels and changes in NSS yields
*/

* command line options
if "`1'"=="" {
	local reload_fred 0
}
else {
	local reload_fred `1'
}


********************************************************************************
********************************************************************************
* load raw FRED data
if `reload_fred' {
n di "loading FRED"
* note: use ALFRED vintages for reproducability
local vintage_date 03oct2021
local vintage_date_varname 20211003

**************************************************
* rate data
n di "Treasury rate data"
local fredvars DGS1MO DGS3MO DGS6MO ///
	DGS1 DGS2 DGS3 DGS5 DGS7 DGS10 DGS20 DGS30
import fred `fredvars' , clear vintage(`vintage_date')
foreach v of local fredvars {
	rename `v'_`vintage_date_varname' `v'
}
drop datestr
rename daten date
sort date
order date
save ../data/fred/_fred_treasury_rates.dta, replace


**************************************************
* additional rate data
* libor: USD3MTD156N discontinued
n di "additional rate data"
local fredvars TEDRATE T10YIE T5YIFR ///
	BAMLC0A1CAAAEY BAMLC0A4CBBBEY BAMLH0A2HYBEY BAMLH0A3HYCEY ///
	DAAA DBAA VXOCLS ///
	USRECD DFEDTARL 
import fred `fredvars' , clear vintage(`vintage_date')
foreach v of local fredvars {
	rename `v'_`vintage_date_varname' `v'
}
drop datestr
rename daten date
sort date
order date
save ../data/fred/_fred_raw.dta, replace


**************************************************
* corporate bond indices (Treasury)
* "high quality market" corporate bond spot rates 
*https://home.treasury.gov/data/treasury-coupon-issues-and-corporate-bond-yield-curve/corporate-bond-yield-curve
* [#]-year: HQMCB[#]1YR
* [#]-year [#]-month: HQMCB[#]Y[#]M
* [#]-month: HQMCB[#]MT
* 6-months to 100 years, 6-month increments
n di "corporate yields (HQM)"

* only get yields for maturities y=1/30
local fredvars ""
forval y=1/30 {
	local fredvars "`fredvars' HQMCB`y'YR"
}
import fred `fredvars' , clear vintage(`vintage_date')
foreach v of local fredvars {
	rename `v'_`vintage_date_varname' `v'
}
drop datestr
rename daten date
sort date
order date
save ../data/fred/_fred_hqm_idxs.dta, replace



**************************************************
* macro data
n di "macro data"
local fredvars PCEPI PCE GDPC1 GDPPOT INDPRO
import fred `fredvars' , clear vintage(`vintage_date')
foreach v of local fredvars {
	rename `v'_`vintage_date_varname' `v'
}
drop datestr
rename daten date
sort date
order date
save ../data/fred/_fred_macro.dta, replace


**************************************************
* balance sheet: 
*https://www.federalreserve.gov/releases/h41/
* treasuries: total, and maturing within:
* >10Y, 5-10Y, 1-5Y, 91D-1Y, 16-90D, <=15D
* MBS: total, and maturing within:
* >10Y, 5-10Y, 1-5Y, 91D-1Y, 16-90D, <=15D
* agency debt: total, and maturing within:
* >10Y, 5-10Y, 1-5Y, 91D-1Y, 16-90D, <=15D
n di "balance sheet data"
* updated to include QT period
local vintage_date 01jan2023
local vintage_date_varname 20230101
local fredvars ///
	TREAST TREAS10Y TREAS5T10 TREAS1T5 TREAS911Y TREAS1590 TREAS15 ///
	WSHOMCB MBS10Y MBS5T10 MBS1T5 MBS911Y MBS1690 MBS15 ///
	FEDDT FEDD10Y FEDD5T10 FEDD1T5 FEDD911Y FEDD1690 FEDD15
import fred `fredvars' , clear vintage(`vintage_date')
foreach v of local fredvars {
	rename `v'_`vintage_date_varname' `v'
}
drop datestr
rename daten date
sort date
order date
save ../data/fred/_fred_balance_sheet.dta, replace





}


********************************************************************************
********************************************************************************
* clean
n di "cleaning FRED"
use ../data/fred/_fred_raw.dta, clear
* create ZLB indicator
gen ZLB = DFEDTARL==0
drop DFEDTARL
* Forward fill data
sort date ZLB USRECD
ds date, not
foreach v of varlist `r(varlist)' {
    replace `v' = `v'[_n-1] if `v'==.
}
* difference data -- fix missing across weekends/holidays
sort date
gen t = _n
tsset t

*  spreads
rename DAAA MD_AAA
rename DBAA MD_BAA

rename BAMLC0A1CAAAEY BAML_AAA
rename BAMLC0A4CBBBEY BAML_BBB
rename BAMLH0A2HYBEY BAML_B
rename BAMLH0A3HYCEY BAML_C

gen BAML_AAA_BBB = BAML_BBB - BAML_AAA 
gen BAML_AAA_B = BAML_B - BAML_AAA 
gen BAML_AAA_C = BAML_C - BAML_AAA 

gen MD_AAA_BAA = MD_BAA - MD_AAA

* dfference data series
ds TEDRATE T10YIE T5YIFR BAML_* MD_AAA MD_BAA MD_AAA_BAA VXOCLS 
foreach v of varlist `r(varlist)' {
	gen D_`v' = D.`v'
}

* keep changes
keep date D_* USRECD VXOCLS ZLB

* labels
label var D_TEDRATE "Ted Rate"
label var D_T10YIE "10Y Infl Exp (TIPS)"
label var D_T5YIFR "5Y Forward Infl Exp (TIPS)"

label var D_BAML_AAA "AAA (BAML)"
label var D_BAML_BBB "BBB (BAML)"
label var D_BAML_B "B (BAML)"
label var D_BAML_C "C (BAML)"
label var D_MD_AAA "AAA (Moody's)"
label var D_MD_BAA "BAA (Moody's)"

label var D_BAML_AAA_BBB "AAA-BBB Spread (BAML)"
label var D_BAML_AAA_B "AAA-B Spread (BAML)"
label var D_BAML_AAA_C "AAA-C Spread (BAML)"
label var D_MD_AAA_BAA "AAA-BAA Spread (Moody's)"

label var VXOCLS "VIX (level)"
label var D_VXOCLS "VIX"

label var USRECD "Recession"
label var ZLB "ZLB"


save ../data/fred/fred_daily_shocks.dta, replace



********************************************************************************
********************************************************************************
* datastream

**************************
* misc spreads
n di "LIBOR/OIS spreads"
use ../data/input_restricted/ds_spreads.dta, clear
replace date = dofc(date)
format date %td
ds date ticker, not
reshape wide `r(varlist)', i(date) j(ticker) string
sort date
gen t = _n
tsset t

* create LIBOR-OIS 3-month spread
gen LIBOR_OIS_spr = PBBUSD3M - POIUSD3M

* daily change
ds date t, not
foreach v of varlist `r(varlist)' {
	gen D_`v' = D.`v'
}
keep date D_*
* labels
label var D_PBBUSD3M "LIBOR"
label var D_POIUSD3M "OIS"
label var D_LIBOR_OIS_spr "LIBOR-OIS"

* to combined datastream file
sort date
save ../data/input/datastream_daily_shocks.dta, replace


**************************
* non-financial commercial paper
n di "Comm paper"
use ../data/input_restricted/ds_comm_paper.dta, clear
replace date = dofc(date)
format date %td
ds date ticker, not
reshape wide `r(varlist)', i(date) j(ticker) string
* just keep 1, 2, 3 month
keep date PFRCP*M
* spreads
forval m=1/3 {
    gen PFRCP`m'M_spr = PFRCP2`m'M - PFRCPN`m'M
}
sort date
gen t = _n
tsset t
* daily change
ds date t, not
foreach v of varlist `r(varlist)' {
	gen D_`v' = D.`v'
}
keep date D_*

* labels
forval m=1/3 {
    label var D_PFRCPN`m'M "`m'-M Commercial Paper (P1)"
    label var D_PFRCP2`m'M "`m'-M Commercial Paper (P2)"
    label var D_PFRCP`m'M_spr "`m'-M Commercial Paper (P2-P1 spread)"
}

* to combined datastream file
merge 1:1 date using ../data/input/datastream_daily_shocks.dta, nogen
save ../data/input/datastream_daily_shocks.dta, replace

**************************
* CDS indices
n di "CDS"
use ../data/input_restricted/ds_cds.dta, clear
replace date = dofc(date)
format date %td
ds date ticker, not
reshape wide `r(varlist)', i(date) j(ticker) string
sort date
gen t = _n
tsset t
* daily change
ds date t, not
foreach v of varlist `r(varlist)' {
	gen D_`v' = D.`v'
}
keep date D_*
* labels
label var D_PUSAUTCD "Automotive CDS"
label var D_PUSBANCD "Banks CDS"
label var D_PUSBASCD "Basic RES CDS"
label var D_PUSCHECD "Chemicals CDS"
label var D_PUSCONCD "Construction Materials CDS"
label var D_PUSFINCD "Financial Services CDS"
label var D_PUSFOOCD "Food and Beverage CDS"
label var D_PUSHEACD "Health Care CDS"
label var D_PUSINDCD "Industrial G&S CDS"
label var D_PUSINSCD "Insurance CDS"
label var D_PUSMEDCD "Media CDS"
label var D_PUSOILCD "Oil and Gas CDS"
label var D_PUSPERCD "Personal HHLD CDS"
label var D_PUSRETCD "Retail CDS"
label var D_PUSTECCD "Technology CDS"
label var D_PUSTELCD "Telecommunications CDS"
label var D_PUSTRACD "Travel Leisure CDS"
label var D_PUSUTICD "Utilities CDS"

* to combined datastream file
merge 1:1 date using ../data/input/datastream_daily_shocks.dta, nogen
sort date
save ../data/input/datastream_daily_shocks.dta, replace



**************************
* Commodities
n di "Commodities"
use ../data/input_restricted/ds_commodities.dta, clear
replace date = dofc(date)
format date %td
ds date ticker, not
reshape wide `r(varlist)', i(date) j(ticker) string
sort date
gen t = _n
tsset t
* log change (pct pts)
ds date t, not
foreach v of varlist `r(varlist)' {
	gen log_`v' = log(`v')
	gen Dlog_`v' = 100*D.log_`v'
}
keep date Dlog_*
* labels
label var Dlog_PGSCITOT "GSCI Total Commodity (log)"
label var Dlog_PGSENTOT "GSCI Energy (log)"
label var Dlog_PGSGCTOT "GSCI Gold (log)"
label var Dlog_PGSCLTOT "GSCI Crude Oil (log)"

* to combined datastream file
merge 1:1 date using ../data/input/datastream_daily_shocks.dta, nogen
sort date
save ../data/input/datastream_daily_shocks.dta, replace


**************************
* Inflation swaps
n di "Infl swaps"
use ../data/input_restricted/ds_infl_swaps.dta, clear
replace date = dofc(date)
format date %td
ds date ticker, not
reshape wide `r(varlist)', i(date) j(ticker) string
sort date
gen t = _n
tsset t

* daily change
ds date t, not
foreach v of varlist `r(varlist)' {
	gen D_`v' = D.`v'
}
keep date D_*

* labels
forval y=1/30 {
    if `y' < 10 {
        local ystr "`y'Y"
    }
    else {
        local ystr "`y'"
    }
    * ICAP inflation swaps
    cap label var D_PICAIS`ystr' "`y'-Y Infl Swap (ICAP)"
    * Thomson Reuters swaps
    cap label var D_PUSIDS`ystr' "`y'-Y Infl Swap (Thomson Reuters)"
}

* to combined datastream file
merge 1:1 date using ../data/input/datastream_daily_shocks.dta, nogen
sort date
save ../data/input/datastream_daily_shocks.dta, replace


**************************
* Equities
n di "Equities"
use ../data/input_restricted/ds_equities.dta, clear
replace date = dofc(date)
format date %td
* fix ticker
replace ticker = "SPCOMP" if ticker=="S&PCOMP"
ds date ticker, not
reshape wide `r(varlist)', i(date) j(ticker) string
sort date
gen t = _n
tsset t
* log change (pct pts)
ds date t, not
foreach v of varlist `r(varlist)' {
	gen log_`v' = log(`v')
	gen Dlog_`v' = 100*D.log_`v'
}
keep date Dlog_*
* label
label var Dlog_PDJCMP65 "Dow Jones Composite (log)"
label var Dlog_PDJINDUS "Dow Jones Industrial (log)"
label var Dlog_PDJUTILS "Dow Jones Utilities (log)"
label var Dlog_PNASA100 "NASDAQ 100 (log)"
label var Dlog_PNASCOMP "NASDAQ Composite (log)"
label var Dlog_PFRUSSL2 "Russell 2000 (log)"
label var Dlog_PSPCOMP "SP500 (log)"

* to combined datastream file
merge 1:1 date using ../data/input/datastream_daily_shocks.dta, nogen
sort date
save ../data/input/datastream_daily_shocks.dta, replace



********************************************************************************
********************************************************************************
* load NSS parameters from GSW data
n di "loading NSS GSW data"

* raw NSS params
import delim ../data/input/GSW_NSS/feds200628.csv, clear delim(",") case(preserve) ///
	rowrange(9:) varnames(9)

keep Date BETA0 BETA1 BETA2 BETA3 TAU1 TAU2
destring BETA0 BETA1 BETA2 BETA3 TAU1 TAU2, ignore("NA") replace
gen date = date(Date, "YMD")
order date
drop Date
* save params
save ../data/input/gsw_nss_params.dta, replace


n di "constructing NSS yield curve data"
drop if missing(BETA0)
format date %td
* construct yields for 1980-; when BETA3 and TAU3 estimates are available
keep if year(date)>=1980

* compute spot and forward rates
forvalues mm=1/30 {
	calc_nss_yield, beta0(BETA0) beta1(BETA1) beta2(BETA2) beta3(BETA3) ///
		tau1(TAU1) tau2(TAU2) m(`mm') zeroname("S_`mm'") forwardname("F_`mm'")
}
drop BETA* TAU*
* save levels
sort date
save ../data/input/nss_levels.dta, replace
* construct changes
sort date
gen t = _n
tsset t
ds date, not
foreach var of varlist `r(varlist)' {
	gen D_`var' = D.`var'
}
keep date D_*
save ../data/input/nss_shocks.dta, replace




********************************************************************************
********************************************************************************
* fed funds futures
n di "FFR continuous contracts"

local ffr_dir ../../data_processing_restricted/Datastream/fed_funds_futures/

* combine all contracts
local tempcounter 0
qui forval i=1994/2021 {
	local tempcounter = `tempcounter'+1
	import delimited using `ffr_dir'/contracts_`i'.csv, varnames(1) clear 
	* first variable from datastream corresponds to date
	* note: error in excel file names depending on OS (PC/mac)
	* use ds to extract first variable
	ds
	local datevar: word 1 of `r(varlist)'
	rename `datevar' datestr
	gen date = date(datestr,"MDY")
	format %td date
	tsset date
	rename *ps *_ps
	rename *po *_po
	rename *vm *_vm
	rename caf01* ffc_1_*
	rename caf02* ffc_2_*
	rename caf03* ffc_3_*
	rename caf04* ffc_4_*
	rename caf05* ffc_5_*
	rename caf06* ffc_6_*
	rename caf07* ffc_7_*
	rename caf08* ffc_8_*
	rename caf09* ffc_9_*
	rename caf10* ffc_10_*
	rename caf11* ffc_11_*
	rename caf12* ffc_12_*
	destring ffc*, force replace
	egen emiss = rowmin(ffc*)
	keep if ~mi(emiss)
	drop emiss
	* TEST BLOCK: see what happens when we replace all instances of contract before first volume w 0
	tempfile temp`tempcounter'
	save `temp`tempcounter''
}

* combine all auctions by year, keep 1994 on
use `temp1', clear
forval i=2/`tempcounter' {
	merge 1:1 date using `temp`i'', nogen
}
order date, first
gen datem = mofd(date)
gen month = mod(datem,12)+1
gen year = yofd(date)
keep if year>=1994

* format variable titles to be easier to loop over
forval i=94/99 {
	rename *_`i'_p* *_19`i'_p*
	rename *_`i'_v* *_19`i'_v*
}
forval i=1/9 {
	rename *_0`i'_p* *_200`i'_p*
	rename *_0`i'_v* *_200`i'_v*
}
forval i=10/21 {
	rename *_`i'_p* *_20`i'_p*
	rename *_`i'_v* *_20`i'_v*
}
rename *_00_p* *_2000_p*
rename *_00_v* *_2000_v*

* replace all instances of prices with missing before first vm trade
gen n = _n
forval y=1994/2021 {
	forval m=1/12 {
		qui sum n if ffc_`m'_`y'_vm>0 & ~mi(ffc_`m'_`y'_vm)
		local first = r(min)
		replace ffc_`m'_`y'_ps = . if n<`first'
		replace ffc_`m'_`y'_po = . if n<`first'
	} 
}
drop n

save ../data/input_restricted/fed_funds_futures_contracts_all.dta, replace

import delimited using `ffr_dir'/continuous.csv, varnames(1) clear
* first variable from datastream corresponds to date
* note: error in excel file names depending on OS (PC/mac), use ds to extract first variable
ds
local datevar: word 1 of `r(varlist)'
rename `datevar' datestr
gen date = date(datestr,"MDY")
format %td date
tsset date
order date, first
destring cafc*, replace force
egen emiss = rowmin(cafc*)
keep if ~mi(emiss)
drop emiss
rename *ps *_ps
rename *po *_po
rename *vm *_vm
drop v*
rename cafcs00* ffc_cont*
save ../data/input_restricted/contract_30d_continuous.dta, replace



*-------------------------------------------------------------------------------
* Create h-month continuous fed funds series by aggregating contracts
*   for all h in {1,2,3,4,5,6,7,8,9,10,11,12}
*-------------------------------------------------------------------------------

* load contracts
use ../data/input_restricted/fed_funds_futures_contracts_all.dta, clear

* generate placeholders for continuous series, varying the horizon in months
forval h=0/12 {
	gen ffr_cont_`h'm_open = .
	gen ffr_cont_`h'm_close = .
	gen ffr_cont_`h'm_vol = .
}

* loop over days, start on jan 1 1994
forval n=1/7000 {
	* month/year today
	local m0 = month[`n']
	local y0 = year[`n']
	* month/year 1 month from now
	local m1 = `m0'+1
	local y1 = `y0'
	if `m1'==13 {
		local m1 = 1
		local y1 = `y1' + 1
	}
	* month/year 2 months from now
	local m2 = `m1'+1
	local y2 = `y1'
	if `m2'==13 {
		local m2 = 1
		local y2 = `y2' + 1
	}
	* month/year 3 months from now
	local m3 = `m2'+1
	local y3 = `y2'
	if `m3'==13 {
		local m3 = 1
		local y3 = `y3' + 1
	}
	* month/year 4 months from now
	local m4 = `m3'+1
	local y4 = `y3'
	if `m4'==13 {
		local m4 = 1
		local y4 = `y4' + 1
	}
	* month/year 5 months from now
	local m5 = `m4'+1
	local y5 = `y4'
	if `m5'==13 {
		local m5 = 1
		local y5 = `y5' + 1
	}
	* month/year 6 months from now
	local m6 = `m5'+1
	local y6 = `y5'
	if `m6'==13 {
		local m6 = 1
		local y6 = `y6' + 1
	}
	* month/year 7 months from now
	local m7 = `m6'+1
	local y7 = `y6'
	if `m7'==13 {
		local m7 = 1
		local y7 = `y7' + 1
	}
	* month/year 8 months from now
	local m8 = `m7'+1
	local y8 = `y7'
	if `m8'==13 {
		local m8 = 1
		local y8 = `y8' + 1
	}
	* month/year 9 months from now
	local m9 = `m8'+1
	local y9 = `y8'
	if `m9'==13 {
		local m9 = 1
		local y9 = `y9' + 1
	}
	* month/year 10 months from now
	local m10 = `m9'+1
	local y10 = `y9'
	if `m10'==13 {
		local m10 = 1
		local y10 = `y10' + 1
	}
	* month/year 11 months from now
	local m11 = `m10'+1
	local y11 = `y10'
	if `m11'==13 {
		local m11 = 1
		local y11 = `y11' + 1
	}
	* month/year 12 months from now
	local m12 = `m11'+1
	local y12 = `y11'
	if `m12'==13 {
		local m12 = 1
		local y12 = `y12' + 1
	}
	* use next month's contract
	forval h=0/12 {
		qui replace ffr_cont_`h'm_open = ffc_`m`h''_`y`h''_po in `n'
		qui replace ffr_cont_`h'm_close = ffc_`m`h''_`y`h''_ps in `n'
		qui replace ffr_cont_`h'm_vol = ffc_`m`h''_`y`h''_vm in `n'
	}
}
keep date ffr_cont*
sort date
gen _T = _n
tsset _T

gen days_tot = 31
replace days_tot = 28 if month(date) == 2
replace days_tot = 30 if month(date) == 4 | month(date) == 6 | month(date) == 9 | month(date) == 11
gen days_left = days_tot - day(date) + 1
gen frac_days_left = days_left/days_tot
gen ffr_cont_0m_scaling = 1/frac_days_left

* Intraday and interday fed funds futures expectations changes (basis points)
forval i=0/12 {
	gen ffr_cont_`i'm_intraday = 100*((100-ffr_cont_`i'm_close) - (100-ffr_cont_`i'm_open))
	gen ffr_cont_`i'm_interday = 100*((100-ffr_cont_`i'm_close) - (100-L.ffr_cont_`i'm_close))
}

keep date ffr_cont*_interday ffr_cont*_vol
* save
save ../data/input/fed_funds_futures_continuous.dta, replace







